package cn.virens.app.common;

public class SQL {
	/** 登录操作 */
	public static final String ON_LINE = "UPDATE `device` SET `imsi`='%s', `iccid`='%s', `online`='1', `on_line_time`=NOW(), `signal0`='%d', `signal1`='%d' WHERE `mac`='%s' LIMIT 1";
	/** 掉线操作 */
	public static final String OFF_LINE = "UPDATE `device` SET `online`='0',`signal0`='-1',`signal1`='-1',`off_line_time` = NOW(),`heatbeat_time` = NULL WHERE `mac`='%s' LIMIT 1";
	/** 心跳检查 */
	public static final String HEARTBEAT = "UPDATE `device` SET `heatbeat_time`=NOW() WHERE `mac`='%s' LIMIT 1";
	/** 修改上报数据 */
	public static final String SENSOR_DATA_UP = "UPDATE `device_data_sensor` SET `tds_one`='%f', `tds_two`='%f', `waterway_one`='%f', `waterway_two`='%f', `solenoidvalve_switch`='%d', `solenoidvalve_off_reason`='%f', `update_time`=NOW() WHERE `device`='%s' LIMIT 1";
	/** 插入上报数据 */
	public static final String SENSOR_DATA_IN = "INSERT INTO `device_data_sensor` (`device`, `tds_one`, `tds_two`, `waterway_one`, `waterway_two`, `surplus_flow`, `solenoidvalve_switch`, `solenoidvalve_off_reason`, `create_time`) VALUES ('%s', '%f', '%f', '%f', '%f','%f', '%d', '%s', NOW())";
	/** 修改设备告警 */
	public static final String WARING_DATA_UP = "UPDATE `device_data_waring` SET `state`='%d', `update_time`=NOW() WHERE `device`='%s' LIMIT 1";
	/** 插入设备告警 */
	public static final String WARING_DATA_IN = "INSERT INTO `device_data_waring` (`device`, `state`, `create_time`) VALUES ('%s', 0, NOW())";
	/** 插入指令 */
	public static final String INSTRUCTION_IN = "INSERT INTO `cmd_history`(`seq`, `mac`, `data`, `status`, `create_time`) VALUES ('%d', '%s', '%s', 0, NOW())";
	/** 插入查询 */
	public static final String INSTRUCTION_SELECT = "SELECT * FROM cmd_history WHERE seq = '%d' AND mac = '%s'";
	/** 更改设备在线状态 */
	public static final String ONLINE_STATUS_UP = "UPDATE `device_online_status` SET `status` = '%d' WHERE `mac` = '%s'";
	/** 删除已有设备状态数据 */
	public static final String ONLINE_STATUS_DE = "DELETE FROM `cmd_history` WHERE `mac` = '%s'";
	/** 更新设备剩余流量 */
	public static final String DEVICE_SURPLUS_FLOW_UP = "UPDATE `device` SET `surplus_flow` = '%d' WHERE `mac` = '%s'";
	/** 更新设备剩余天数 */
	public static final String DEVICE_SURPLUS_DAYS_UP = "UPDATE `device` SET `surplus_days` = '%d' WHERE `mac` = '%s'";
	/** 更新设备滤芯信息 */
	public static final String FILTER_ATTRIBUTE_SETTING_UP = "UPDATE `device_filter` SET `days` = '%d', `expire_time` = '%s' WHERE device = '%s' AND `status` = 1";
	/** 更新设备所有信息 */
	public static final String DEVICE_SETTING_UP = "";
	/** 插入设备连接情况 */
	public static final String RECORD_IN = "INSERT INTO `device_connect_record`(`device`, `type`, `remark`, `create_time`, `update_time`) VALUES ('%s', '%d', '%s', NOW(), NOW())";
}
